﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

using ISS.CaseStudy.DBUtility;
using ISS.CaseStudy.Entities;


namespace ISS.CaseStudy.DAL
{
    public static class CaseStudyDLL
    {
        public static DataSet GetAllInfos(string orderByName, bool asc)
        {
            string sql = "select * from CaseStudyInfo";
            if (!string.IsNullOrEmpty(orderByName))
            {
                sql += " order by " + orderByName;

                if (!asc)
                {
                    sql += " desc";
                }
            }
            DataSet dataSet = Database.SpExecuteDataSet(sql);
            return dataSet;
        }

        public static int GetCountOf(string columName)
        {
            string sql = string.Format("select count(distinct {0}) from CaseStudyInfo where {1} <> ''", columName, columName);
            return int.Parse(Database.SpExecuteScalar(sql));
        }

        public static CaseStudyInfo GetInfo(string id)
        {
            string sql = "select * from CaseStudyInfo where [Id]=@id";
            SqlParameter[] paramCollection = { new SqlParameter("@id", id) };
            DataSet dataSet = Database.SpExecuteDataSet(sql, paramCollection);
            DataTable dataTable = dataSet.Tables[0];
            if (dataTable.Rows.Count == 0)
            {
                return null;
            }

            DataRow row = dataTable.Rows[0];

            CaseStudyInfo caseStudyInfo = new CaseStudyInfo();
            caseStudyInfo.Id = id;
            caseStudyInfo.Title = row["Title"].ToString();
            caseStudyInfo.Description = row["Description"].ToString();
            caseStudyInfo.FileName = row["FileName"].ToString();
            caseStudyInfo.ImageAddress = row["Image"].ToString();
            if (!string.IsNullOrEmpty(row["CreateTime"].ToString()))
            {
                caseStudyInfo.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
            }

            if (!string.IsNullOrEmpty(row["ModifyTime"].ToString()))
            {
                caseStudyInfo.ModifyTime = DateTime.Parse(row["ModifyTime"].ToString());
            }

            caseStudyInfo.Technology = row["Technology"].ToString();
            caseStudyInfo.IndustryValue = row["Industry"].ToString();
            caseStudyInfo.ClientValue = row["Client"].ToString();
            caseStudyInfo.ServiceValue = row["Service"].ToString();
            caseStudyInfo.Capability = row["Capability"].ToString();
            caseStudyInfo.Quote = row["Quote"].ToString();
            caseStudyInfo.Location = row["Location"].ToString();
            caseStudyInfo.VisitCount = row["VisitCount"].ToString();
            caseStudyInfo.OrganizationSize = row["OrganizationSize"].ToString();
            caseStudyInfo.OrganizationProfile = row["OrganizationProfile"].ToString();
            caseStudyInfo.CountryAndRegion = row["CountryAndRegion"].ToString();

            return caseStudyInfo;
        }

        public static bool Update(CaseStudyInfo caseStudyInfo)
        {
            string sql = "UPDATE CaseStudyInfo SET [Title]=@Title, [Description]=@Description, [FileName]=@FileName, [Image]=@Image, [ModifyTime]=@ModifyTime, [Technology]=@Technology, [Industry]=@Industry , [Client]=@Client, [Service]=@Service, [Capability]=@Capability, [Quote]=@Quote , [Location]=@Location, [VisitCount]=@ViewCount, [OrganizationSize]=@OrganizationSize, [OrganizationProfile]=@OrganizationProfile, [CountryAndRegion]=@Country where ([Id]=@Id)";
            SqlParameter[] paramCollection = { new SqlParameter("@Title", caseStudyInfo.Title), 
                                                 new SqlParameter("@Description", caseStudyInfo.Description), 
                                                 new SqlParameter("@FileName", caseStudyInfo.FileName), 
                                                 new SqlParameter("@Image", caseStudyInfo.ImageAddress),
                                                 new SqlParameter("@ModifyTime", caseStudyInfo.ModifyTime.ToString()),
                                                 new SqlParameter("@Technology", caseStudyInfo.Technology),
                                                 new SqlParameter("@Industry", caseStudyInfo.IndustryValue),
                                                 new SqlParameter("@Client", caseStudyInfo.ClientValue),
                                                 new SqlParameter("@Service", caseStudyInfo.ServiceValue),
                                                 new SqlParameter("@Capability", caseStudyInfo.Capability),
                                                 new SqlParameter("@Quote", caseStudyInfo.Quote),
                                                 new SqlParameter("@Location", caseStudyInfo.Location),
                                                 new SqlParameter("@ViewCount", caseStudyInfo.VisitCount),
                                                 new SqlParameter("@OrganizationSize", caseStudyInfo.OrganizationSize),
                                                 new SqlParameter("@OrganizationProfile", caseStudyInfo.OrganizationProfile),
                                                 new SqlParameter("@Country", caseStudyInfo.CountryAndRegion),
                                                 new SqlParameter("@Id", caseStudyInfo.Id)};
            int i = Database.SpExecuteNonQuery(sql, paramCollection);
            if (i > 0)
            {
                return true;
            }

            return false;
        }

        public static bool Delete(string id)
        {
            bool b = false;
            string sql = "delete from CaseStudyInfo where ([Id]=@Id)";
            SqlParameter[] paramCollection = { new SqlParameter("@Id", id) };
            int i = Database.SpExecuteNonQuery(sql, paramCollection);
            if (i > 0)
            {
                b = true;
            }
            return b;
        }

        public static bool Insert(CaseStudyInfo caseStudyInfo, out string errorDescription, out int id)
        {
            string errorCode = string.Empty;
            errorDescription = string.Empty;
            id = -1;

            string procedureName = "up_CaseStudy_Add";
            SqlParameter errorCodeParam = new SqlParameter("@ErrorCode", SqlDbType.VarChar, 2, errorCode);
            errorCodeParam.Direction = ParameterDirection.Output;

            SqlParameter errorDescriptionParam = new SqlParameter("@ErrorDescription", SqlDbType.VarChar, 50, errorDescription);
            errorDescriptionParam.Direction = ParameterDirection.Output;

            SqlParameter caseStudyIdParam = new SqlParameter("@NewCaseStudyID", id);
            caseStudyIdParam.Direction = ParameterDirection.Output;

            SqlParameter[] paramCollection = { new SqlParameter("@CSTitle", caseStudyInfo.Title), 
                                                 new SqlParameter("@Description", caseStudyInfo.Description), 
                                                 new SqlParameter("@FileName", caseStudyInfo.FileName), 
                                                 new SqlParameter("@Image", caseStudyInfo.ImageAddress),
                                                 new SqlParameter("@Technology", caseStudyInfo.Technology),
                                                 new SqlParameter("@IndustryType", caseStudyInfo.IndustryType),
                                                 new SqlParameter("@Industry", caseStudyInfo.IndustryValue),
                                                 new SqlParameter("@ClientType", caseStudyInfo.ClientType),
                                                 new SqlParameter("@Client", caseStudyInfo.ClientValue),
                                                 new SqlParameter("@ServiceType", caseStudyInfo.ServiceType),
                                                 new SqlParameter("@Service", caseStudyInfo.ServiceValue),
                                                 new SqlParameter("@RelatedInformation", caseStudyInfo.RelatedInformation),
                                                 new SqlParameter("@Capability", caseStudyInfo.Capability),
                                                 new SqlParameter("@Quote", caseStudyInfo.Quote),
                                                 new SqlParameter("@Location", caseStudyInfo.Location),
                                                 new SqlParameter("@ViewCount", caseStudyInfo.VisitCount),
                                                 new SqlParameter("@OrganizationSize", int.Parse(caseStudyInfo.OrganizationSize)),
                                                 new SqlParameter("@OrganizationProfile", caseStudyInfo.OrganizationProfile),
                                                 new SqlParameter("@CountryAndRegion", caseStudyInfo.CountryAndRegion),
                                                 errorCodeParam,
                                                 errorDescriptionParam,
                                                 caseStudyIdParam
                                             };

            int i = Database.ExecuteNonQuery(procedureName, paramCollection);

            errorCode = errorCodeParam.Value.ToString();
            errorDescription = errorDescriptionParam.Value.ToString();
            id = int.Parse(caseStudyIdParam.Value.ToString());

            if (errorCode != "00")
            {
                return false;
            }

            return true;
        }

        public static bool Update(CaseStudyInfo caseStudyInfo, out string errorDescription)
        {
            errorDescription = string.Empty;
            string errorCode = string.Empty;
            string procedureName = "up_CaseStudy_Update";
            SqlParameter errorCodeParam = new SqlParameter("@ErrorCode", SqlDbType.VarChar, 2, errorCode);
            errorCodeParam.Direction = ParameterDirection.Output;

            SqlParameter errorDescriptionParam = new SqlParameter("@ErrorDescription", SqlDbType.VarChar, 50, errorDescription);
            errorDescriptionParam.Direction = ParameterDirection.Output;

            SqlParameter[] paramCollection = { 
                                                 new SqlParameter("@Id", caseStudyInfo.Id), 
                                                 new SqlParameter("@CSTitle", caseStudyInfo.Title), 
                                                 new SqlParameter("@Description",  caseStudyInfo.Description), 
                                                 new SqlParameter("@FileName", caseStudyInfo.FileName), 
                                                 new SqlParameter("@Image", caseStudyInfo.ImageAddress),
                                                 new SqlParameter("@Technology", caseStudyInfo.Technology),
                                                 new SqlParameter("@IndustryType", caseStudyInfo.IndustryType),
                                                 new SqlParameter("@Industry", caseStudyInfo.IndustryValue),
                                                 new SqlParameter("@ClientType", caseStudyInfo.ClientType),
                                                 new SqlParameter("@Client", caseStudyInfo.ClientValue),
                                                 new SqlParameter("@ServiceType", caseStudyInfo.ServiceType),
                                                 new SqlParameter("@Service", caseStudyInfo.ServiceValue),
                                                 new SqlParameter("@RelatedInformation", caseStudyInfo.RelatedInformation),
                                                 new SqlParameter("@Capability", caseStudyInfo.Capability),
                                                 new SqlParameter("@Quote", caseStudyInfo.Quote),
                                                 new SqlParameter("@Location", caseStudyInfo.Location),
                                                 new SqlParameter("@ViewCount", caseStudyInfo.VisitCount),
                                                 new SqlParameter("@OrganizationSize", int.Parse(caseStudyInfo.OrganizationSize)),
                                                 new SqlParameter("@OrganizationProfile", caseStudyInfo.OrganizationProfile),
                                                 new SqlParameter("@CountryAndRegion", caseStudyInfo.CountryAndRegion),
                                                 errorCodeParam,
                                                 errorDescriptionParam
                                             };

            Database.ExecuteNonQuery(procedureName, paramCollection);

            errorCode = errorCodeParam.Value.ToString();
            errorDescription = errorDescriptionParam.Value.ToString();

            if (errorCode != "00")
            {
                return false;
            }

            return true;
        }

        public static DataSet GetOrderedCaseStudies(CaseStudyOrder orderName)
        {
            if (orderName == CaseStudyOrder.Client)
            {
                return Database.ExecuteDataSet("up_GetCaseStudies_OrderByClient");
            }
            else if (orderName == CaseStudyOrder.Industry)
            {
                return Database.ExecuteDataSet("up_GetCaseStudies_OrderByIndustry");
            }
            else
            {
                return Database.ExecuteDataSet("up_GetCaseStudies_OrderByService");
            }
        }
    }
}
